﻿using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using ESpace.DalTools.Common;
using System.Data.SQLite;
using System.Text.RegularExpressions;

namespace ESpace.DalTools.SQLite
{
	public class BuilderBaseSQLite : BuilderBase
	{
		#region 属性定义
		private string _DBName;
		public string DBName
		{
			get
			{
				return _DBName;
			}
			set
			{
				_DBName = value;
			}
		}

		private string _DBServer;
		public string DBServer
		{
			get
			{
				return _DBServer;
			}
			set
			{
				_DBServer = value;
			}
		}

		private string _DBUid;
		public string DBUid
		{
			get
			{
				return _DBUid;
			}
			set
			{
				_DBUid = value;
			}
		}

		private string _DBPassword;
		public string DBPassword
		{
			get
			{
				return _DBPassword;
			}
			set
			{
				_DBPassword = value;
			}
		}

		protected SQLiteConnection _conn;
		private XmlDocument domSql2DotNet;
		#endregion

		// 构造函数
		public BuilderBaseSQLite()
		{
			// 读取配置文件
			string sSplit = System.AppDomain.CurrentDomain.BaseDirectory.Substring(System.AppDomain.CurrentDomain.BaseDirectory.Length - 1);
			string ConfigPath;
			if (sSplit != @"\" && sSplit != "/")
				ConfigPath = System.AppDomain.CurrentDomain.BaseDirectory + @"\";
			else
				ConfigPath = System.AppDomain.CurrentDomain.BaseDirectory;
			string sConfigFileName = ConfigPath + @"Reflection\SQL2DotNet.xml";
			domSql2DotNet = new XmlDocument();
			domSql2DotNet.Load(sConfigFileName);
		}

		/// <summary>
		/// 根据sql类型获取DotNet语言类型
		/// </summary>
		/// <param name="sSqlType">sql类型</param>
		/// <returns>DotNet语言类型</returns>
		protected override string GetLanguageTypeName(string sSqlType)
		{
			XmlNode xmlNode = domSql2DotNet.SelectSingleNode("DataBase/" + sSqlType);

			//获取节点的属性，并循环取出需要的属性值
			XmlAttributeCollection xmlAttr = xmlNode.Attributes;
			string strReturn = "";
			for (int i = 0; i < xmlAttr.Count; i++)
			{
				if (xmlAttr.Item(i).Name == "langType")
				{
					strReturn = xmlAttr.Item(i).Value;
					break;
				}
			}
			return strReturn;
		}

		/// <summary>
		/// 把SqlServer中的类型名称转换成.Net语言中SqlDBType的类型
		/// </summary>
		/// <param name="sSqlType">Sql类型</param>
		/// <returns>.Net中SqlType类型</returns>
		protected override string GetDbTypeName(string sSqlType)
		{
			XmlNode xmlNode = domSql2DotNet.SelectSingleNode("DataBase/" + sSqlType);

			//获取节点的属性，并循环取出需要的属性值
			XmlAttributeCollection xmlAttr = xmlNode.Attributes;
			string strReturn = "";
			for (int i = 0; i < xmlAttr.Count; i++)
			{
				if (xmlAttr.Item(i).Name == "dbType")
				{
					strReturn = xmlAttr.Item(i).Value;
					break;
				}
			}
			return strReturn;
		}

		/// <summary>
		/// 判断Sql类型是否允许定义长度
		/// </summary>
		/// <param name="sSqlType">Sql类型</param>
		/// <returns>
		/// true，允许。false，不允许
		/// </returns>
		protected override bool IfTypeAllowLength(string sSqlType)
		{
			XmlNode xmlNode = domSql2DotNet.SelectSingleNode("DataBase/" + sSqlType);

			//获取节点的属性，并循环取出需要的属性值
			XmlAttributeCollection xmlAttr = xmlNode.Attributes;
			string strReturn = "";
			for (int i = 0; i < xmlAttr.Count; i++)
			{
				if (xmlAttr.Item(i).Name == "allowLengthDef")
				{
					strReturn = xmlAttr.Item(i).Value;
					break;
				}
			}
			return strReturn == "1";
		}

		/// <summary>
		/// 连接到数据库
		/// </summary>
		public override void ConnectToDB()
		{
			string sConnString = ConnectionString;
			_conn = new SQLiteConnection(sConnString);

			_conn.Open();
		}

		/// <summary>
		/// 断开数据库连接
		/// </summary>
		public override void DisconnectFromDB()
		{
			if (_conn.State == ConnectionState.Open)
			{
				_conn.Close();
			}
		}


		/// <summary>
		/// 获取数据库中所有表
		/// </summary>
		/// <returns>数据库中包含的表</returns>
		public override DataTable GetAllTables()
		{

			//SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
			SQLiteCommand sqliteComm = new SQLiteCommand("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name", _conn);
			sqliteComm.CommandType = CommandType.Text;
			SQLiteDataAdapter sdaTables = new SQLiteDataAdapter(sqliteComm);
			DataTable dtTables = new DataTable();
			sdaTables.Fill(dtTables);
			return dtTables;
		}

		/// <summary>
		/// 获取表的基本信息
		/// </summary>
		/// <param name="TableName"></param>
		/// <returns></returns>
		protected override DataTable GetATable(string TableName)
		{
			string strSql = "PRAGMA table_info("+TableName+")";

			SQLiteCommand sqlComm = new SQLiteCommand(strSql, _conn);

			sqlComm.CommandType = CommandType.Text;

			SQLiteDataAdapter sdaTables = new SQLiteDataAdapter(sqlComm);
			DataTable dtTables = new DataTable();
			sdaTables.Fill(dtTables);

			return dtTables;
		}

		/// <summary>
		/// 获取用户表
		/// </summary>
		/// <returns>数据库中的用户表</returns>
		public override DataTable GetUserTables()
		{
			string strSql = "select name from sqlite_master where type='table' order by name";

			SQLiteCommand sqlComm = new SQLiteCommand(strSql, _conn);

			sqlComm.CommandType = CommandType.Text;

			SQLiteDataAdapter sdaTables = new SQLiteDataAdapter(sqlComm);
			DataTable dtTables = new DataTable();
			sdaTables.Fill(dtTables);

			return dtTables;
		}


		/// <summary>
		/// 根据表名获取字段列表
		/// </summary>
		/// <param name="sTableName">表名</param>
		/// <returns>字段列表</returns>
		public override DataTable GetColumns(string sTableName)
		{

			string strSql = "PRAGMA table_info(" + sTableName + ")";

			SQLiteCommand sqlComm = new SQLiteCommand(strSql, _conn);

			sqlComm.CommandType = CommandType.Text;

			SQLiteDataAdapter sdaTables = new SQLiteDataAdapter(sqlComm);
			DataTable dtColumn = new DataTable("Columns");
			sdaTables.Fill(dtColumn);

			// 添加扩展列
			dtColumn.Columns.Add("ColumnName", Type.GetType("System.String"));
			dtColumn.Columns.Add("Precision", Type.GetType("System.String"));
			dtColumn.Columns.Add("Scale", Type.GetType("System.String"));
			dtColumn.Columns.Add("MaxLength", Type.GetType("System.String"));
			dtColumn.Columns.Add("IsNullable", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("IsRowGUIDCol", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("IsIdentity", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("IsComputed", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("IsXmlDocument", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("IsPK", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("IsLengthDef", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("LanguageType", Type.GetType("System.String"));
			dtColumn.Columns.Add("DbType", Type.GetType("System.String"));
			dtColumn.Columns.Add("HbmType", Type.GetType("System.String"));
			dtColumn.Columns.Add("Desc", Type.GetType("System.String"));

			// 获取表的主键
			//DataTable dtPkey = GetPrimaryKeys(sTableName);
		
			foreach (DataRow row in dtColumn.Rows)
			{
				// 判断列是否GUID列
				if (row["type"].ToString()=="GUID")
					row["IsRowGUIDCol"] = 1;
				else
					row["IsRowGUIDCol"] = 0;

				
				row["IsPK"] = row["pk"];

				///TODO:判断主键有问题
				if (row["pk"].ToString() == "1" && (string)row["type"] == "INTEGER")
				{
					row["IsIdentity"] = "1";
					row["type"] = "int";
				}
				else
				{
					row["IsIdentity"] = "0";
				}

				row["IsNullable"] =row["notnull"];

				string sDataType, sMaxLength, sPreci,sScale = "";
				
				TypeNameProcess((string)row["type"], out sDataType, out sMaxLength, out sPreci, out sScale);

				bool bIfAllowLength = IfTypeAllowLength(sDataType.ToLower());
				row["IsLengthDef"] = bIfAllowLength;
				row["LanguageType"] = GetLanguageTypeName(sDataType.ToLower());
				row["DbType"] = GetDbTypeName(sDataType.ToLower());
				row["HbmType"] = GetHbmTypeName(sDataType.ToLower());
				row["Desc"] = "";
				row["Precision"] = sPreci;
				row["Scale"] = sScale;
				row["MaxLength"] = sMaxLength;
				row["ColumnName"] = (string)row["name"];
				//row["IsIdentity"] = "0";
			
			
			}
			//删除原始数据信息
			dtColumn.Columns.Remove("cid");
			dtColumn.Columns.Remove("name");
			dtColumn.Columns.Remove("type");
			dtColumn.Columns.Remove("notnull");
			dtColumn.Columns.Remove("dflt_value");
			dtColumn.Columns.Remove("pk");
			return dtColumn;
		}

		#region 以下方法没有实现


		/// <summary>
		/// 根据SqlServer的数据类型的编号，取得数据类型名称。
		/// 如1对应于char，2对应于numeric
		/// </summary>
		/// <param name="sSqlDataTypeID">数据类型编号</param>
		/// <returns>数据类型的名称</returns>
		protected string GetSqlDataType(short iSqlDataTypeID)
		{
			//SqlCommand sqlComm = new SqlCommand("sp_datatype_info", _conn);
			//sqlComm.CommandType = CommandType.StoredProcedure;
			//SqlParameter sqlParam = new SqlParameter("@data_type", SqlDbType.Int);
			//sqlParam.Value = iSqlDataTypeID;
			//sqlComm.Parameters.Add(sqlParam);
			//return (string)sqlComm.ExecuteScalar();
			return null;
		}
		/// <summary>
		/// 获取指定表的主键
		/// </summary>
		/// <param name="sTableName">表名</param>
		/// <returns>主键列表</returns>
		protected override DataTable GetPrimaryKeys(string sTableName)
		{
			return null;
		}

		/// <summary>
		/// 获取一个表的外键
		/// </summary>
		/// <param name="sTableName">表名</param>
		/// <returns>表的所有外键</returns>
		protected override XmlDocument GetForeignKeys(string sTableName)
		{
			
			return null;
		}

		/// <summary>
		/// 获取用户自定义存储过程列表
		/// </summary>
		/// <returns></returns>
		public override DataTable GetStoreProcedures()
		{
			return null;
		}

		protected override DataTable GetAStoreProcedure(string ProdecureName)
		{
			return null;
		}

		/// <summary>
		/// 获取用户自定义函数列表
		/// </summary>
		/// <returns></returns>
		protected override DataTable GetUserDefineFunctions()
		{
			return null;
		}

		/// <summary>
		/// 获取函数或存储过程的参数列表
		/// </summary>
		/// <param name="sSpName">函数或存储过程名称</param>
		/// <returns>参数列表</returns>
		protected override DataTable GetStoreProcedureColumns(string sSpName)
		{
			return null;
		}
		#endregion


		private void TypeNameProcess(string strName, out string TypeName, out string Length, out string Preci, out string Scale)
		{
			TypeName = strName;
			int n = strName.IndexOf("(");
			Length = "";
			Preci = "";
			Scale = "";
			if (n > 0)
			{
				TypeName = strName.Substring(0, n);
				switch (TypeName.Trim().ToUpper())
				{					

					//只有大小(M)
					case "TINYINT":
					case "SMALLINT":
					case "MEDIUMINT":
					case "INT":
					case "INTEGER":
					case "BIGINT":
					case "TIMESTAMP":
					case "CHAR":
					case "VARCHAR":
					case "NVARCHAR":
						{
							int m = strName.IndexOf(")");
							Length = strName.Substring(n + 1, m - n - 1);
						}
						break;
					case "FLOAT"://(M,D)
					case "DOUBLE":
					case "REAL":
					case "DECIMAL":
					case "DEC":
					case "NUMERIC":
						{
							int m = strName.IndexOf(")");
							string strlen = strName.Substring(n + 1, m - n - 1);
							int i = strlen.IndexOf(",");
							Length = strlen.Substring(0, i);
							Scale = strlen.Substring(i + 1);
						}
						break;
					case "ENUM"://(M1,M2,M3)
					case "SET":
						{
						}
						break;
					default:
						break;
				}
			}

		}


		private static string GetHbmTypeName(string systype)
		{


			string HbmType = systype;
			switch (systype.Trim().ToLower())
			{

				case "string":
				case "nvarchar":
				case "nchar":
				case "ntext":
					HbmType = "String";
					break;
				case "text":
				case "char":
				case "varchar":
					HbmType = "AnsiString";
					break;
				case "datetime":
				case "smalldatetime":
					HbmType = "DateTime";
					break;
				case "smallint":
					HbmType = "Int16";
					break;
				case "tinyint":
					HbmType = "Byte";
					break;
				case "int":
					HbmType = "Int32";
					break;
				case "bigint":
				case "long":
					HbmType = "Int64";
					break;
				case "float":
					HbmType = "Double";
					break;
				case "real":
				case "numeric":
				case "decimal":
					HbmType = "Decimal";
					break;
				case "money":
				case "smallmoney":
					HbmType = "Currency";
					break;
				case "bool":
				case "bit":
					HbmType = "Boolean";
					break;
				case "binary":
				case "varbinary":
					HbmType = "Binary";
					break;
				case "image":
					HbmType = "Image";
					break;
				case "uniqueidentifier":
					HbmType = "Guid";
					break;
				case "timestamp":
					HbmType = "String";
					break;
				default:
					HbmType = "String";
					break;
			}
			return HbmType;
		}

	}

}
